{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9aa76533",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Python3_Jupyter_Nb_人事招聘数据分析.ipynb\n",
    "# Create By GF 2023-11-20 17:40"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9ab860fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import pyecharts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8dedd1a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "from Python3_Business_Indicators import *\n",
    "from Python3_DataProcFunc import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b99ebb6b",
   "metadata": {},
   "outputs": [],
   "source": [
    "pyecharts.globals.CurrentConfig.ONLINE_HOST = pyecharts.globals.OnlineHostType.NOTEBOOK_HOST"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4f53320d",
   "metadata": {},
   "outputs": [],
   "source": [
    "Entry = pd.read_excel(\"./人事月报基础数据_2023-09-30.xlsx\", sheet_name=\"入职\")\n",
    "Depart = pd.read_excel(\"./人事月报基础数据_2023-09-30.xlsx\", sheet_name=\"离职\")\n",
    "Basic = pd.read_excel(\"./人事月报基础数据_2023-09-30.xlsx\", sheet_name=\"人事数据\")\n",
    "Profit = pd.read_excel(\"./人事月报基础数据_2023-09-30.xlsx\", sheet_name=\"毛利\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "df58ec6a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 列重命名\n",
    "Rename_Entry = Entry\n",
    "Rename_Depart = Depart\n",
    "Rename_Basic = Basic\n",
    "Rename_Profit = Profit\n",
    "# --------------------------------------------------\n",
    "Rename_Entry = Rename_Entry.rename(columns={\"事务日期\":\"日期\", \"业务职员代码\":\"职员代码\"})\n",
    "Rename_Depart = Rename_Depart.rename(columns={\"事务日期\":\"日期\", \"业务职员代码\":\"职员代码\"})\n",
    "Rename_Profit = Rename_Profit.rename(columns={\"业务职员代码\":\"职员代码\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "ca2c1b54",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>职员代码</th>\n",
       "      <th>入职日期</th>\n",
       "      <th>转正日期</th>\n",
       "      <th>本月评级时间</th>\n",
       "      <th>本月评级</th>\n",
       "      <th>评级23</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>P1544-16</td>\n",
       "      <td>2015-09-01</td>\n",
       "      <td>2015-11-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>P2292-23</td>\n",
       "      <td>2005-09-23</td>\n",
       "      <td>2005-12-23</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>P275-6</td>\n",
       "      <td>2007-05-11</td>\n",
       "      <td>2007-06-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>P100-12</td>\n",
       "      <td>2003-06-24</td>\n",
       "      <td>2003-12-01</td>\n",
       "      <td>2019-08-01</td>\n",
       "      <td>无</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>P2105-40</td>\n",
       "      <td>2021-11-01</td>\n",
       "      <td>2022-04-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1976</th>\n",
       "      <td>CQ1-2212005</td>\n",
       "      <td>2022-12-14</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>2023-02-01</td>\n",
       "      <td>新</td>\n",
       "      <td>新</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1977</th>\n",
       "      <td>CQ1-1402006</td>\n",
       "      <td>2021-03-11</td>\n",
       "      <td>2021-04-01</td>\n",
       "      <td>2022-01-02</td>\n",
       "      <td>无</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978</th>\n",
       "      <td>CQ1-2102012</td>\n",
       "      <td>2021-02-22</td>\n",
       "      <td>2021-04-01</td>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>中</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1979</th>\n",
       "      <td>CQ1-1903036</td>\n",
       "      <td>2019-03-20</td>\n",
       "      <td>2019-05-01</td>\n",
       "      <td>2023-09-01</td>\n",
       "      <td>优</td>\n",
       "      <td>中</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1980</th>\n",
       "      <td>CQ1-2302050</td>\n",
       "      <td>2023-02-25</td>\n",
       "      <td>2023-05-01</td>\n",
       "      <td>2023-09-01</td>\n",
       "      <td>优</td>\n",
       "      <td>中</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1981 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             职员代码       入职日期       转正日期     本月评级时间 本月评级 评级23\n",
       "0        P1544-16 2015-09-01 2015-11-01 1900-01-01  NaN  NaN\n",
       "1        P2292-23 2005-09-23 2005-12-23 1900-01-01  NaN  NaN\n",
       "2          P275-6 2007-05-11 2007-06-01 1900-01-01  NaN  NaN\n",
       "3         P100-12 2003-06-24 2003-12-01 2019-08-01    无  NaN\n",
       "4        P2105-40 2021-11-01 2022-04-01 1900-01-01  NaN  NaN\n",
       "...           ...        ...        ...        ...  ...  ...\n",
       "1976  CQ1-2212005 2022-12-14 1900-01-01 2023-02-01    新    新\n",
       "1977  CQ1-1402006 2021-03-11 2021-04-01 2022-01-02    无  NaN\n",
       "1978  CQ1-2102012 2021-02-22 2021-04-01 2023-01-01    中  NaN\n",
       "1979  CQ1-1903036 2019-03-20 2019-05-01 2023-09-01    优    中\n",
       "1980  CQ1-2302050 2023-02-25 2023-05-01 2023-09-01    优    中\n",
       "\n",
       "[1981 rows x 6 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 筛选字段\n",
    "Entry_Filed_Name = [\"日期\", \"事务类别\", \"职位\"]\n",
    "Depart_Filed_Name = [\"日期\", \"事务类别\", \"职位\"]\n",
    "Basic_Filed_Name = [\"职员代码\", \"入职日期\", \"转正日期\", \"本月评级时间\", \"本月评级\", \"评级23\"]\n",
    "Profit_Filed_Name = [\"日期\", \"职员代码\", \"考核毛利\"]\n",
    "# --------------------------------------------------\n",
    "Filter_Entry = Rename_Entry[Entry_Filed_Name]\n",
    "Filter_Depart = Rename_Depart[Depart_Filed_Name]\n",
    "Filter_Basic = Rename_Basic[Basic_Filed_Name]\n",
    "Filter_Profit = Rename_Profit[Profit_Filed_Name]\n",
    "# --------------------------------------------------\n",
    "Filter_Entry = Filter_Entry[Filter_Entry[\"事务类别\"] == \"职员入职\"]\n",
    "Filter_Entry = Filter_Entry[Filter_Entry[\"职位\"].str.contains(\"导购\")]\n",
    "Filter_Depart = Filter_Depart[Filter_Depart[\"事务类别\"] == \"职员离职\"]\n",
    "Filter_Depart = Filter_Depart[Filter_Depart[\"职位\"].str.contains(\"导购\")]\n",
    "# --------------------------------------------------\n",
    "Filter_Basic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "e4680c54",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 类型调整\n",
    "ChangeType_Entry = Filter_Entry.copy()\n",
    "ChangeType_Depart = Filter_Depart.copy()\n",
    "ChangeType_Basic = Filter_Basic.copy()\n",
    "ChangeType_Profit = Filter_Profit.copy()\n",
    "# --------------------------------------------------\n",
    "ChangeType_Entry[\"日期\"] = ChangeType_Entry[\"日期\"].dt.strftime(\"%Y-%m-%d\").astype(\"datetime64[ns]\")\n",
    "ChangeType_Depart[\"日期\"] = ChangeType_Depart[\"日期\"].dt.strftime(\"%Y-%m-%d\").astype(\"datetime64[ns]\")\n",
    "ChangeType_Basic[\"入职日期\"] = ChangeType_Basic[\"入职日期\"].dt.strftime(\"%Y-%m-%d\").astype(\"datetime64[ns]\")\n",
    "ChangeType_Basic[\"转正日期\"] = ChangeType_Basic[\"转正日期\"].dt.strftime(\"%Y-%m-%d\").astype(\"datetime64[ns]\")\n",
    "ChangeType_Basic[\"本月评级时间\"] = ChangeType_Basic[\"本月评级时间\"].dt.strftime(\"%Y-%m-%d\").astype(\"datetime64[ns]\")\n",
    "ChangeType_Profit[\"日期\"] = ChangeType_Profit[\"日期\"].dt.strftime(\"%Y-%m-%d\").astype(\"datetime64[ns]\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "a46ab26d-c67b-4220-b884-93be20a8261b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>入职人数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-01-03</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-01-05</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-01-06</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-01-07</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263</th>\n",
       "      <td>2023-10-28</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>264</th>\n",
       "      <td>2023-10-29</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265</th>\n",
       "      <td>2023-11-01</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>266</th>\n",
       "      <td>2023-11-02</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267</th>\n",
       "      <td>2023-11-03</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>268 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            日期  入职人数\n",
       "0   2023-01-01     6\n",
       "1   2023-01-03     3\n",
       "2   2023-01-05     1\n",
       "3   2023-01-06     2\n",
       "4   2023-01-07     1\n",
       "..         ...   ...\n",
       "263 2023-10-28     2\n",
       "264 2023-10-29     2\n",
       "265 2023-11-01    10\n",
       "266 2023-11-02     1\n",
       "267 2023-11-03     1\n",
       "\n",
       "[268 rows x 2 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# [入职人数]字段计算\n",
    "EntryCount = ChangeType_Entry.copy()\n",
    "# --------------------------------------------------\n",
    "EntryCount = EntryCount.groupby(\"日期\").count().reset_index().rename(columns={\"事务类别\":\"入职人数\"}) # -> 聚合\"入职\"表格。\n",
    "EntryCount = EntryCount[[\"日期\", \"入职人数\"]]\n",
    "EntryCount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "33b7a3ee-cd03-40f9-9c69-f6bd142216a3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>离职人数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-01-02</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-01-04</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-01-05</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-01-06</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280</th>\n",
       "      <td>2023-10-30</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>281</th>\n",
       "      <td>2023-10-31</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>282</th>\n",
       "      <td>2023-11-01</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>283</th>\n",
       "      <td>2023-11-02</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>284</th>\n",
       "      <td>2023-11-03</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>285 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            日期  离职人数\n",
       "0   2023-01-01    12\n",
       "1   2023-01-02     1\n",
       "2   2023-01-04     3\n",
       "3   2023-01-05     3\n",
       "4   2023-01-06     3\n",
       "..         ...   ...\n",
       "280 2023-10-30     2\n",
       "281 2023-10-31     3\n",
       "282 2023-11-01    20\n",
       "283 2023-11-02     2\n",
       "284 2023-11-03     1\n",
       "\n",
       "[285 rows x 2 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# [离职人数]字段计算\n",
    "DepartCount = ChangeType_Depart.copy()\n",
    "# --------------------------------------------------\n",
    "DepartCount = DepartCount.groupby(\"日期\").count().reset_index().rename(columns={\"事务类别\":\"离职人数\"}) # -> 聚合\"入职\"表格。\n",
    "DepartCount = DepartCount[[\"日期\", \"离职人数\"]]\n",
    "DepartCount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "622c83b9-dfbe-4615-a921-23d2c11a1972",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>职员代码</th>\n",
       "      <th>入职日期</th>\n",
       "      <th>转正日期</th>\n",
       "      <th>本月评级时间</th>\n",
       "      <th>本月评级</th>\n",
       "      <th>评级23</th>\n",
       "      <th>本年转正</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>P23013-60</td>\n",
       "      <td>2023-03-27</td>\n",
       "      <td>2023-07-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>P23019-12</td>\n",
       "      <td>2023-09-12</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>CQ0-0711002</td>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>YN4-0606013</td>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>P23014-15</td>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1971</th>\n",
       "      <td>CQ1-2318008</td>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>2023-09-01</td>\n",
       "      <td>新</td>\n",
       "      <td>新</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1972</th>\n",
       "      <td>CQ1-2309014</td>\n",
       "      <td>2023-09-12</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1974</th>\n",
       "      <td>CQ1-2302024</td>\n",
       "      <td>2023-02-11</td>\n",
       "      <td>2023-05-01</td>\n",
       "      <td>2023-09-01</td>\n",
       "      <td>优</td>\n",
       "      <td>中</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1975</th>\n",
       "      <td>CQ1-2303003</td>\n",
       "      <td>2023-03-01</td>\n",
       "      <td>2023-05-01</td>\n",
       "      <td>2023-06-01</td>\n",
       "      <td>优</td>\n",
       "      <td>中</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1980</th>\n",
       "      <td>CQ1-2302050</td>\n",
       "      <td>2023-02-25</td>\n",
       "      <td>2023-05-01</td>\n",
       "      <td>2023-09-01</td>\n",
       "      <td>优</td>\n",
       "      <td>中</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>948 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             职员代码       入职日期       转正日期     本月评级时间 本月评级 评级23 本年转正\n",
       "6       P23013-60 2023-03-27 2023-07-01 1900-01-01  NaN  NaN    Y\n",
       "11      P23019-12 2023-09-12 1900-01-01 1900-01-01  NaN  NaN    N\n",
       "14    CQ0-0711002 2023-01-01 1900-01-01 1900-01-01  NaN  NaN    N\n",
       "15    YN4-0606013 2023-01-01 1900-01-01 1900-01-01  NaN  NaN    N\n",
       "16      P23014-15 2023-01-01 1900-01-01 1900-01-01  NaN  NaN    N\n",
       "...           ...        ...        ...        ...  ...  ...  ...\n",
       "1971  CQ1-2318008 2023-08-06 1900-01-01 2023-09-01    新    新    N\n",
       "1972  CQ1-2309014 2023-09-12 1900-01-01 1900-01-01  NaN  NaN    N\n",
       "1974  CQ1-2302024 2023-02-11 2023-05-01 2023-09-01    优    中    Y\n",
       "1975  CQ1-2303003 2023-03-01 2023-05-01 2023-06-01    优    中    Y\n",
       "1980  CQ1-2302050 2023-02-25 2023-05-01 2023-09-01    优    中    Y\n",
       "\n",
       "[948 rows x 7 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# [本年转正]字段计算\n",
    "# --------------------------------------------------\n",
    "ThisYearRegular = ChangeType_Basic.copy()\n",
    "# --------------------------------------------------\n",
    "ThisYearRegular = ThisYearRegular[ThisYearRegular[\"入职日期\"].dt.year == 2023]\n",
    "ThisYearRegularRowIdxList = ThisYearRegular[ThisYearRegular[\"转正日期\"] >= ThisYearRegular[\"入职日期\"]].index\n",
    "ThisYearLeftOutRowIdxList = ThisYearRegular[ThisYearRegular[\"转正日期\"] == \"1900-01-01\"].index\n",
    "# --------------------------------------------------\n",
    "ThisYearRegular[\"本年转正\"] = None\n",
    "ThisYearRegular.loc[ThisYearRegularRowIdxList, \"本年转正\"] = \"Y\"\n",
    "ThisYearRegular.loc[ThisYearLeftOutRowIdxList, \"本年转正\"] = \"N\"\n",
    "ThisYearRegular"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "30b37e4b-93b3-476c-8635-011a202ce36d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>转正均毛</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-06-01</td>\n",
       "      <td>400.943662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-06-02</td>\n",
       "      <td>440.060606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-06-03</td>\n",
       "      <td>457.317757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-06-04</td>\n",
       "      <td>399.018248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-06-05</td>\n",
       "      <td>452.993548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>2023-09-26</td>\n",
       "      <td>515.691748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>2023-09-27</td>\n",
       "      <td>471.830729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>2023-09-28</td>\n",
       "      <td>495.531616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>2023-09-29</td>\n",
       "      <td>870.792763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>712.379791</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>122 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            日期        转正均毛\n",
       "0   2023-06-01  400.943662\n",
       "1   2023-06-02  440.060606\n",
       "2   2023-06-03  457.317757\n",
       "3   2023-06-04  399.018248\n",
       "4   2023-06-05  452.993548\n",
       "..         ...         ...\n",
       "117 2023-09-26  515.691748\n",
       "118 2023-09-27  471.830729\n",
       "119 2023-09-28  495.531616\n",
       "120 2023-09-29  870.792763\n",
       "121 2023-09-30  712.379791\n",
       "\n",
       "[122 rows x 2 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# [转正均毛]字段计算\n",
    "# --------------------------------------------------\n",
    "ThisYearRegularStaffProfit = pd.merge(left=ChangeType_Profit, right=ThisYearRegular, how=\"left\", on=\"职员代码\")\n",
    "# --------------------------------------------------\n",
    "ThisYearRegularStaffProfit = ThisYearRegularStaffProfit[ThisYearRegularStaffProfit[\"本年转正\"] == \"Y\"]\n",
    "# --------------------------------------------------\n",
    "ThisYearRegularStaffDayMeanProfit = ThisYearRegularStaffProfit.groupby(\"日期\", as_index=False).mean().rename(columns={\"考核毛利\":\"转正均毛\"})\n",
    "ThisYearRegularStaffDayMeanProfit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "635cd9a0-e565-44b8-8f7c-4ad10dace655",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>淘汰均毛</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-06-01</td>\n",
       "      <td>368.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-06-02</td>\n",
       "      <td>283.400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-06-03</td>\n",
       "      <td>263.480000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-06-04</td>\n",
       "      <td>218.204545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-06-05</td>\n",
       "      <td>258.121951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>2023-09-26</td>\n",
       "      <td>436.043103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>2023-09-27</td>\n",
       "      <td>412.677885</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>2023-09-28</td>\n",
       "      <td>365.513514</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>2023-09-29</td>\n",
       "      <td>729.380117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>615.151429</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>122 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            日期        淘汰均毛\n",
       "0   2023-06-01  368.500000\n",
       "1   2023-06-02  283.400000\n",
       "2   2023-06-03  263.480000\n",
       "3   2023-06-04  218.204545\n",
       "4   2023-06-05  258.121951\n",
       "..         ...         ...\n",
       "117 2023-09-26  436.043103\n",
       "118 2023-09-27  412.677885\n",
       "119 2023-09-28  365.513514\n",
       "120 2023-09-29  729.380117\n",
       "121 2023-09-30  615.151429\n",
       "\n",
       "[122 rows x 2 columns]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# [淘汰均毛]字段计算\n",
    "# --------------------------------------------------\n",
    "ThisYearLeftOutStaffProfit = pd.merge(left=ChangeType_Profit, right=ThisYearRegular, how=\"left\", on=\"职员代码\")\n",
    "# --------------------------------------------------\n",
    "ThisYearLeftOutStaffProfit = ThisYearLeftOutStaffProfit[ThisYearLeftOutStaffProfit[\"本年转正\"] == \"N\"]\n",
    "# --------------------------------------------------\n",
    "ThisYearLeftOutStaffDayMeanProfit = ThisYearLeftOutStaffProfit.groupby(\"日期\", as_index=False).mean().rename(columns={\"考核毛利\":\"淘汰均毛\"})\n",
    "ThisYearLeftOutStaffDayMeanProfit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "a8e8a44c-6df8-48d1-aeae-efa5909250b2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>评级转换</th>\n",
       "      <th>数量</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-02-01</td>\n",
       "      <td>Down</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-02-01</td>\n",
       "      <td>Up</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-03-01</td>\n",
       "      <td>Down</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-03-01</td>\n",
       "      <td>Up</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-04-01</td>\n",
       "      <td>Down</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2023-04-01</td>\n",
       "      <td>Up</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2023-05-01</td>\n",
       "      <td>Down</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2023-05-01</td>\n",
       "      <td>Up</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2023-06-01</td>\n",
       "      <td>Down</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2023-06-01</td>\n",
       "      <td>Up</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2023-07-01</td>\n",
       "      <td>Down</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2023-08-01</td>\n",
       "      <td>Down</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2023-08-01</td>\n",
       "      <td>Up</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2023-09-01</td>\n",
       "      <td>Down</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2023-09-01</td>\n",
       "      <td>Up</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           日期  评级转换   数量\n",
       "0  2023-02-01  Down    7\n",
       "1  2023-02-01    Up    2\n",
       "2  2023-03-01  Down    4\n",
       "3  2023-03-01    Up    5\n",
       "4  2023-04-01  Down    4\n",
       "5  2023-04-01    Up    3\n",
       "6  2023-05-01  Down    9\n",
       "7  2023-05-01    Up    1\n",
       "8  2023-06-01  Down    4\n",
       "9  2023-06-01    Up    8\n",
       "10 2023-07-01  Down   10\n",
       "11 2023-08-01  Down    8\n",
       "12 2023-08-01    Up    3\n",
       "13 2023-09-01  Down  110\n",
       "14 2023-09-01    Up   44"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# [评级转换]字段计算\n",
    "# --------------------------------------------------\n",
    "RateConvert = ChangeType_Basic.copy()\n",
    "# --------------------------------------------------\n",
    "RateConvert[\"评级转换\"] = None\n",
    "# --------------------------------------------------\n",
    "for Idx in RateConvert.index:\n",
    "    if (RateConvert.loc[Idx, \"评级转换\"] == None):\n",
    "        Staff_ID = RateConvert.loc[Idx, \"职员代码\"]\n",
    "        Rate23 = RateConvert.loc[Idx, \"评级23\"]\n",
    "        Rate00 = RateConvert.loc[Idx, \"本月评级\"]\n",
    "        # ------------------------------------------\n",
    "        if (Rate23 == \"待\") and (Rate00 == \"中\"): RateConvert.loc[Idx, \"评级转换\"] = \"Up\"\n",
    "        if (Rate23 == \"待\") and (Rate00 == \"优\"): RateConvert.loc[Idx, \"评级转换\"] = \"Up\"\n",
    "        if (Rate23 == \"中\") and (Rate00 == \"优\"): RateConvert.loc[Idx, \"评级转换\"] = \"Up\"\n",
    "        # ------------------------------------------\n",
    "        if (Rate23 == \"优\") and (Rate00 == \"中\"): RateConvert.loc[Idx, \"评级转换\"] = \"Down\"\n",
    "        if (Rate23 == \"优\") and (Rate00 == \"待\"): RateConvert.loc[Idx, \"评级转换\"] = \"Down\"\n",
    "        if (Rate23 == \"中\") and (Rate00 == \"待\"): RateConvert.loc[Idx, \"评级转换\"] = \"Down\"\n",
    "        # ------------------------------------------\n",
    "        Filtered_DataFrame   = RateConvert[RateConvert[\"职员代码\"] == Staff_ID]\n",
    "        Filtered_Rows_Number = Filtered_DataFrame[\"职员代码\"].count()\n",
    "        Filtered_Index_List  = Filtered_DataFrame.index\n",
    "        # ------------------------------------------\n",
    "        if (Filtered_Rows_Number >= 2): # -> 人事评级按月变动。\n",
    "            Filtered_Min_Date = Filtered_DataFrame[\"本月评级时间\"].min()\n",
    "            Filtered_Max_Date = Filtered_DataFrame[\"本月评级时间\"].max()\n",
    "            Filtered_Old_Rate = RateConvert.loc[Filtered_Min_Date, \"本月评级\"]\n",
    "            Filtered_New_Rate = RateConvert.loc[Filtered_Max_Date, \"本月评级\"]\n",
    "            # --------------------------------------\n",
    "            if (Filtered_Old_Rate == \"待\") and (Filtered_New_Rate == \"中\"): RateConvert.loc[Idx, \"评级转换\"] = \"Up\"\n",
    "            if (Filtered_Old_Rate == \"待\") and (Filtered_New_Rate == \"优\"): RateConvert.loc[Idx, \"评级转换\"] = \"Up\"\n",
    "            if (Filtered_Old_Rate == \"中\") and (Filtered_New_Rate == \"优\"): RateConvert.loc[Idx, \"评级转换\"] = \"Up\"\n",
    "            # --------------------------------------\n",
    "            if (Filtered_Old_Rate == \"优\") and (Filtered_New_Rate == \"中\"): RateConvert.loc[Idx, \"评级转换\"] = \"Down\"\n",
    "            if (Filtered_Old_Rate == \"优\") and (Filtered_New_Rate == \"待\"): RateConvert.loc[Idx, \"评级转换\"] = \"Down\"\n",
    "            if (Filtered_Old_Rate == \"中\") and (Filtered_New_Rate == \"待\"): RateConvert.loc[Idx, \"评级转换\"] = \"Down\"\n",
    "    else:\n",
    "        pass\n",
    "RateConvert = RateConvert.groupby(by=[\"本月评级时间\", \"评级转换\"], as_index=False).count().rename(columns={\"职员代码\":\"数量\"})\n",
    "RateConvert = RateConvert[[\"本月评级时间\", \"评级转换\", \"数量\"]].rename(columns={\"本月评级时间\":\"日期\"})\n",
    "RateConvertUp = RateConvert[RateConvert[\"评级转换\"] == \"Up\"].rename(columns={\"数量\":\"评级上升数\"})\n",
    "RateConvertUp = RateConvertUp[[\"日期\", \"评级上升数\"]]\n",
    "RateConvertDown = RateConvert[RateConvert[\"评级转换\"] == \"Down\"].rename(columns={\"数量\":\"评级下降数\"})\n",
    "RateConvertDown = RateConvertDown[[\"日期\", \"评级下降数\"]]\n",
    "RateConvert"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "674de83a-65be-40e8-a6b5-96793541bb3c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>日均毛利</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-06-01</td>\n",
       "      <td>481.540779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-06-02</td>\n",
       "      <td>444.082764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-06-03</td>\n",
       "      <td>534.941620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-06-04</td>\n",
       "      <td>519.575916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-06-05</td>\n",
       "      <td>464.990603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>2023-09-26</td>\n",
       "      <td>479.636950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>2023-09-27</td>\n",
       "      <td>541.106627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>2023-09-28</td>\n",
       "      <td>545.998926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>2023-09-29</td>\n",
       "      <td>950.756660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>774.451693</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>122 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            日期        日均毛利\n",
       "0   2023-06-01  481.540779\n",
       "1   2023-06-02  444.082764\n",
       "2   2023-06-03  534.941620\n",
       "3   2023-06-04  519.575916\n",
       "4   2023-06-05  464.990603\n",
       "..         ...         ...\n",
       "117 2023-09-26  479.636950\n",
       "118 2023-09-27  541.106627\n",
       "119 2023-09-28  545.998926\n",
       "120 2023-09-29  950.756660\n",
       "121 2023-09-30  774.451693\n",
       "\n",
       "[122 rows x 2 columns]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# [日均毛利]字段计算\n",
    "# --------------------------------------------------\n",
    "DayMeanProfit = ChangeType_Profit.copy()\n",
    "# --------------------------------------------------\n",
    "DayMeanProfit = DayMeanProfit.groupby(\"日期\").mean().reset_index().rename(columns={\"考核毛利\":\"日均毛利\"}) # -> 聚合\"毛利\"表格。\n",
    "DayMeanProfit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "b437a45f-cb4b-48aa-b33f-450e0e0a4d62",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>入职人数</th>\n",
       "      <th>离职人数</th>\n",
       "      <th>日均毛利</th>\n",
       "      <th>转正均毛</th>\n",
       "      <th>淘汰均毛</th>\n",
       "      <th>评级上升数</th>\n",
       "      <th>评级下降数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>6.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-01-02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-01-03</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-01-04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-01-05</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268</th>\n",
       "      <td>2023-09-26</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>479.636950</td>\n",
       "      <td>515.691748</td>\n",
       "      <td>436.043103</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>2023-09-27</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>541.106627</td>\n",
       "      <td>471.830729</td>\n",
       "      <td>412.677885</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270</th>\n",
       "      <td>2023-09-28</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>545.998926</td>\n",
       "      <td>495.531616</td>\n",
       "      <td>365.513514</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>271</th>\n",
       "      <td>2023-09-29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>950.756660</td>\n",
       "      <td>870.792763</td>\n",
       "      <td>729.380117</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>272</th>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>774.451693</td>\n",
       "      <td>712.379791</td>\n",
       "      <td>615.151429</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>273 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            日期  入职人数  离职人数        日均毛利        转正均毛        淘汰均毛  评级上升数  评级下降数\n",
       "0   2023-01-01   6.0  12.0         NaN         NaN         NaN    NaN    NaN\n",
       "1   2023-01-02   NaN   1.0         NaN         NaN         NaN    NaN    NaN\n",
       "2   2023-01-03   3.0   NaN         NaN         NaN         NaN    NaN    NaN\n",
       "3   2023-01-04   NaN   3.0         NaN         NaN         NaN    NaN    NaN\n",
       "4   2023-01-05   1.0   3.0         NaN         NaN         NaN    NaN    NaN\n",
       "..         ...   ...   ...         ...         ...         ...    ...    ...\n",
       "268 2023-09-26   4.0   2.0  479.636950  515.691748  436.043103    NaN    NaN\n",
       "269 2023-09-27   4.0   3.0  541.106627  471.830729  412.677885    NaN    NaN\n",
       "270 2023-09-28   1.0   3.0  545.998926  495.531616  365.513514    NaN    NaN\n",
       "271 2023-09-29   NaN   2.0  950.756660  870.792763  729.380117    NaN    NaN\n",
       "272 2023-09-30   NaN   NaN  774.451693  712.379791  615.151429    NaN    NaN\n",
       "\n",
       "[273 rows x 8 columns]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 连接查询\n",
    "LineTime = DataProcFunc_Pandas_Create_Date_Series_DataFrame(\"2023-01-01\", \"2023-09-30\")\n",
    "LineTime = LineTime[[\"日期\"]]\n",
    "# --------------------------------------------------\n",
    "Joined = pd.merge(left=LineTime, right=EntryCount,                        how=\"left\", on=\"日期\")\n",
    "Joined = pd.merge(left=Joined,   right=DepartCount,                       how=\"left\", on=\"日期\")\n",
    "Joined = pd.merge(left=Joined,   right=DayMeanProfit,                     how=\"left\", on=\"日期\")\n",
    "Joined = pd.merge(left=Joined,   right=ThisYearRegularStaffDayMeanProfit, how=\"left\", on=\"日期\")\n",
    "Joined = pd.merge(left=Joined,   right=ThisYearLeftOutStaffDayMeanProfit, how=\"left\", on=\"日期\")\n",
    "Joined = pd.merge(left=Joined,   right=RateConvertUp,                     how=\"left\", on=\"日期\")\n",
    "Joined = pd.merge(left=Joined,   right=RateConvertDown,                   how=\"left\", on=\"日期\")\n",
    "Joined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "4ccdf92b-d910-44d8-a6ec-a8b890fafe15",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>入职人数</th>\n",
       "      <th>离职人数</th>\n",
       "      <th>日均毛利</th>\n",
       "      <th>转正均毛</th>\n",
       "      <th>淘汰均毛</th>\n",
       "      <th>评级上升数</th>\n",
       "      <th>评级下降数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>6.00</td>\n",
       "      <td>12.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-01-02</td>\n",
       "      <td>5.42</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-01-03</td>\n",
       "      <td>3.00</td>\n",
       "      <td>4.56</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-01-04</td>\n",
       "      <td>5.42</td>\n",
       "      <td>3.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-01-05</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268</th>\n",
       "      <td>2023-09-26</td>\n",
       "      <td>4.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>479.636950</td>\n",
       "      <td>515.691748</td>\n",
       "      <td>436.043103</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>2023-09-27</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>541.106627</td>\n",
       "      <td>471.830729</td>\n",
       "      <td>412.677885</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270</th>\n",
       "      <td>2023-09-28</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>545.998926</td>\n",
       "      <td>495.531616</td>\n",
       "      <td>365.513514</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>271</th>\n",
       "      <td>2023-09-29</td>\n",
       "      <td>5.42</td>\n",
       "      <td>2.00</td>\n",
       "      <td>950.756660</td>\n",
       "      <td>870.792763</td>\n",
       "      <td>729.380117</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>272</th>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>5.42</td>\n",
       "      <td>4.56</td>\n",
       "      <td>774.451693</td>\n",
       "      <td>712.379791</td>\n",
       "      <td>615.151429</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>273 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            日期  入职人数   离职人数        日均毛利        转正均毛        淘汰均毛  评级上升数  评级下降数\n",
       "0   2023-01-01  6.00  12.00         NaN         NaN         NaN    NaN    NaN\n",
       "1   2023-01-02  5.42   1.00         NaN         NaN         NaN    NaN    NaN\n",
       "2   2023-01-03  3.00   4.56         NaN         NaN         NaN    NaN    NaN\n",
       "3   2023-01-04  5.42   3.00         NaN         NaN         NaN    NaN    NaN\n",
       "4   2023-01-05  1.00   3.00         NaN         NaN         NaN    NaN    NaN\n",
       "..         ...   ...    ...         ...         ...         ...    ...    ...\n",
       "268 2023-09-26  4.00   2.00  479.636950  515.691748  436.043103    NaN    NaN\n",
       "269 2023-09-27  4.00   3.00  541.106627  471.830729  412.677885    NaN    NaN\n",
       "270 2023-09-28  1.00   3.00  545.998926  495.531616  365.513514    NaN    NaN\n",
       "271 2023-09-29  5.42   2.00  950.756660  870.792763  729.380117    NaN    NaN\n",
       "272 2023-09-30  5.42   4.56  774.451693  712.379791  615.151429    NaN    NaN\n",
       "\n",
       "[273 rows x 8 columns]"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 空值填充\n",
    "Filled = Joined.copy()\n",
    "# --------------------------------------------------\n",
    "Filled[\"入职人数\"] = Filled[\"入职人数\"].fillna(value=round(Filled[\"入职人数\"].mean(), 2))\n",
    "Filled[\"离职人数\"] = Filled[\"离职人数\"].fillna(value=round(Filled[\"离职人数\"].mean(), 2))\n",
    "Filled"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "0b8c6015",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>入职人数</th>\n",
       "      <th>离职人数</th>\n",
       "      <th>日均毛利</th>\n",
       "      <th>转正均毛</th>\n",
       "      <th>淘汰均毛</th>\n",
       "      <th>评级上升数</th>\n",
       "      <th>评级下降数</th>\n",
       "      <th>离职率</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>6.00</td>\n",
       "      <td>12.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-01-02</td>\n",
       "      <td>5.42</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-01-03</td>\n",
       "      <td>3.00</td>\n",
       "      <td>4.56</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-01-04</td>\n",
       "      <td>5.42</td>\n",
       "      <td>3.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-01-05</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268</th>\n",
       "      <td>2023-09-26</td>\n",
       "      <td>4.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>479.636950</td>\n",
       "      <td>515.691748</td>\n",
       "      <td>436.043103</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>2023-09-27</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>541.106627</td>\n",
       "      <td>471.830729</td>\n",
       "      <td>412.677885</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270</th>\n",
       "      <td>2023-09-28</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>545.998926</td>\n",
       "      <td>495.531616</td>\n",
       "      <td>365.513514</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>271</th>\n",
       "      <td>2023-09-29</td>\n",
       "      <td>5.42</td>\n",
       "      <td>2.00</td>\n",
       "      <td>950.756660</td>\n",
       "      <td>870.792763</td>\n",
       "      <td>729.380117</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>272</th>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>5.42</td>\n",
       "      <td>4.56</td>\n",
       "      <td>774.451693</td>\n",
       "      <td>712.379791</td>\n",
       "      <td>615.151429</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0040</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>273 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            日期  入职人数   离职人数        日均毛利        转正均毛        淘汰均毛  评级上升数  评级下降数  \\\n",
       "0   2023-01-01  6.00  12.00         NaN         NaN         NaN    NaN    NaN   \n",
       "1   2023-01-02  5.42   1.00         NaN         NaN         NaN    NaN    NaN   \n",
       "2   2023-01-03  3.00   4.56         NaN         NaN         NaN    NaN    NaN   \n",
       "3   2023-01-04  5.42   3.00         NaN         NaN         NaN    NaN    NaN   \n",
       "4   2023-01-05  1.00   3.00         NaN         NaN         NaN    NaN    NaN   \n",
       "..         ...   ...    ...         ...         ...         ...    ...    ...   \n",
       "268 2023-09-26  4.00   2.00  479.636950  515.691748  436.043103    NaN    NaN   \n",
       "269 2023-09-27  4.00   3.00  541.106627  471.830729  412.677885    NaN    NaN   \n",
       "270 2023-09-28  1.00   3.00  545.998926  495.531616  365.513514    NaN    NaN   \n",
       "271 2023-09-29  5.42   2.00  950.756660  870.792763  729.380117    NaN    NaN   \n",
       "272 2023-09-30  5.42   4.56  774.451693  712.379791  615.151429    NaN    NaN   \n",
       "\n",
       "        离职率  \n",
       "0    0.0106  \n",
       "1    0.0009  \n",
       "2    0.0040  \n",
       "3    0.0027  \n",
       "4    0.0027  \n",
       "..      ...  \n",
       "268  0.0018  \n",
       "269  0.0027  \n",
       "270  0.0027  \n",
       "271  0.0018  \n",
       "272  0.0040  \n",
       "\n",
       "[273 rows x 9 columns]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 指标计算\n",
    "# --------------------------------------------------\n",
    "Filled[\"离职率\"] = round(Filled[\"离职人数\"] / 1130, 4)\n",
    "# --------------------------------------------------\n",
    "Entry_EMA5_List = BizInd_EMA(5, Filled[\"入职人数\"].values.tolist())\n",
    "Depart_EMA5_List = BizInd_EMA(10, Filled[\"离职人数\"].values.tolist())\n",
    "Depart_Rate_EMA5_List = BizInd_EMA(5, Filled[\"离职率\"].values.tolist())\n",
    "Depart_Rate_EMA10_List = BizInd_EMA(10, Filled[\"离职率\"].values.tolist())\n",
    "Filled"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "4e2df4bb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'C:\\\\Jupyter\\\\render.html'"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Line_Norm = pyecharts.charts.Line()\n",
    "Line_Norm.add_xaxis(Filled[\"日期\"].dt.strftime(\"%Y-%m-%d\").values.tolist())\n",
    "Line_Norm.add_yaxis(\"入职人数\", Filled[\"入职人数\"].values.tolist(), color=\"red\")\n",
    "Line_Norm.add_yaxis(\"离职人数\", Filled[\"离职人数\"].values.tolist(), color=\"green\")\n",
    "Line_Norm.set_global_opts( title_opts = pyecharts.options.TitleOpts(title=\"入离职趋势 (Normal)\", pos_top=\"2%\"),\n",
    "                          legend_opts = pyecharts.options.LegendOpts(pos_top=\"2%\"))\n",
    "Line_Norm.render()\n",
    "# --------------------------------------------------\n",
    "Line_EMA = pyecharts.charts.Line()\n",
    "Line_EMA.add_xaxis(Filled[\"日期\"].dt.strftime(\"%Y-%m-%d\").values.tolist())\n",
    "Line_EMA.add_yaxis(\"入职人数\", Entry_EMA5_List, color=\"red\")\n",
    "Line_EMA.add_yaxis(\"离职人数\", Depart_EMA5_List, color=\"green\")\n",
    "Line_EMA.set_global_opts( title_opts = pyecharts.options.TitleOpts(title=\"入离职趋势 (EMA)\", pos_top=\"22%\"),\n",
    "                         legend_opts = pyecharts.options.LegendOpts(pos_top=\"22%\"))\n",
    "Line_EMA.render()\n",
    "# --------------------------------------------------\n",
    "Line_Depart_Rate = pyecharts.charts.Line()\n",
    "Line_Depart_Rate.add_xaxis(Filled[\"日期\"].dt.strftime(\"%Y-%m-%d\").values.tolist())\n",
    "Line_Depart_Rate.add_yaxis(\"离职率\", Filled[\"离职率\"].values.tolist(), color=\"blue\")\n",
    "Line_Depart_Rate.set_global_opts( title_opts = pyecharts.options.TitleOpts(title=\"离职率趋势 (None)\", pos_top=\"42%\"),\n",
    "                                 legend_opts = pyecharts.options.LegendOpts(pos_top=\"42%\"))\n",
    "Line_Depart_Rate.render()\n",
    "# --------------------------------------------------\n",
    "Line_Depart_Rate_EMA = pyecharts.charts.Line()\n",
    "Line_Depart_Rate_EMA.add_xaxis(Filled[\"日期\"].dt.strftime(\"%Y-%m-%d\").values.tolist())\n",
    "Line_Depart_Rate_EMA.add_yaxis(\"离职率 (EMA5)\",  Depart_Rate_EMA5_List, color=\"black\")\n",
    "Line_Depart_Rate_EMA.add_yaxis(\"离职率 (EMA10)\", Depart_Rate_EMA10_List, color=\"orange\")\n",
    "Line_Depart_Rate_EMA.set_global_opts( title_opts = pyecharts.options.TitleOpts(title=\"离职率指数平均 (EMA)\", pos_top=\"62%\"),\n",
    "                                     legend_opts = pyecharts.options.LegendOpts(pos_top=\"62%\"))\n",
    "Line_Depart_Rate_EMA.render()\n",
    "# --------------------------------------------------\n",
    "Grid_Area = pyecharts.charts.Grid(pyecharts.options.InitOpts(width=\"960px\", height=\"1920px\")) # -> Grid切分画布\n",
    "Grid_Area.add(Line_Norm,            grid_opts = pyecharts.options.GridOpts(pos_top=\"5%\", pos_bottom=\"85%\"))\n",
    "Grid_Area.add(Line_EMA,             grid_opts = pyecharts.options.GridOpts(pos_top=\"25%\", pos_bottom=\"65%\"))\n",
    "Grid_Area.add(Line_Depart_Rate,     grid_opts = pyecharts.options.GridOpts(pos_top=\"45%\", pos_bottom=\"45%\"))\n",
    "Grid_Area.add(Line_Depart_Rate_EMA, grid_opts = pyecharts.options.GridOpts(pos_top=\"65%\", pos_bottom=\"25%\"))\n",
    "Grid_Area.render()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "ed3d5c23-68a9-4b15-a7e7-d6ac63b94d6d",
   "metadata": {},
   "outputs": [],
   "source": [
    "Filled.to_csv(\"./XunJie_Human_Resources_Calculate_Data.csv\", index=False, encoding=\"gbk\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c55ef9f4-92e3-415a-af8c-991aa5124688",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
